import mysql from 'mysql2/promise';
import { config } from './env';

// 创建连接池，只在连接层设置时区
export const pool = mysql.createPool({
  host: config.db.host,
  port: config.db.port,
  user: config.db.user,
  password: config.db.password,
  database: config.db.name,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  charset: 'utf8mb4',
  timezone: '+08:00',   // 连接层设置北京时间，INSERT/UPDATE时NOW()自动使用
  dateStrings: true     // 返回字符串避免JS时区转换
});

// 格式化时间
export function formatDate(date: Date): string {
  const pad = (n: number) => n.toString().padStart(2, '0');
  return `${date.getFullYear()}-${pad(date.getMonth() + 1)}-${pad(date.getDate())} ` +
         `${pad(date.getHours())}:${pad(date.getMinutes())}:${pad(date.getSeconds())}`;
}

// 可选的查询封装函数（仅在需要时使用）
export async function executeQuery(sql: string, params?: any[]): Promise<any> {
  const connection = await pool.getConnection();
  try {
    await connection.execute("SET time_zone = '+08:00'");
    const [rows] = await connection.execute(sql, params || []);
    return rows;
  } finally {
    connection.release();
  }
}

// 插入带时间的函数示例
export async function insertWithNow() {
  const now = new Date();
  const formatted = formatDate(now); // eg: 2025-07-07 15:30:00
  await executeQuery(
    'INSERT INTO logs (message, create_time) VALUES (?, ?)',
    ['测试日志', formatted]
  );
}
